import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import model.UserInfo;
import model.Store;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class ExcelUtil
{
    private static final String PREFIX_FILENAME = "UserInfo";
    private static final String[] names = {"ling", "yi", "er", "san", "si", "wu", "liu", "qi", "ba", "jiu"};
    public static List<UserInfo> getBookItem()
    {
        List<UserInfo> itemList = new ArrayList<UserInfo>();
        
        try
        {
            /*
            for (int i = 0; i < 180; i++)
            {
                BookItem item = new BookItem();
                item.setGovernmentId("ZZ2422" + i);
                item.setSku("16H");
                item.setLastName("ZHU");
                item.setFirstName(getFirstName(i));
                String prefixEmail = Store.getInstance().getStoreMap(Store.DEFAULT_STORE_ID).get(Store.PREFIX_EMAIL);
                item.setPrefixEmail(prefixEmail);
                item.setEmailAddress(getEmail(i + 1, prefixEmail));
                item.setPhoneNumber(getPhoneNumber(i + 1));
                item.setStoreNumber(Store.getInstance().getStoreMap(Store.DEFAULT_STORE_ID).get(Store.STORE_NUM));
                item.setStoreName(Store.getInstance().getStoreMap(Store.DEFAULT_STORE_ID).get(Store.STORE_NAME));
                item.setProductName("iPad");
                item.setQuantity("2");
                LogUtil.getInstance().writeLog(item.toString());
                itemList.add(item);
            }
            */
            File dir = new File(".");
            if (dir != null && dir.isDirectory())
            {
                File[] files = dir.listFiles();
                for (int i = 0; i < files.length; i++)
                {
                    File file = files[i];
                    if (file.getName().toLowerCase().startsWith(PREFIX_FILENAME.toLowerCase()))
                    {
                        addUserInfo(file, itemList);
                    }
                }
            }
        }
        catch (Exception e)
        {
            LogUtil.getInstance().writeLog("ExcelUtil getBookItem error:" + e.toString());
        }
        return itemList;
    }
    
    private static void addUserInfo(File file, List<UserInfo> itemList)
    {
        LogUtil.getInstance().writeLog("addUserInfo:" + file.getAbsolutePath());
        try
        {
            String[][] result = getData(file, 1);
            String fileName = file.getName();
            String storeId = Store.DEFAULT_STORE_ID;
            if (PREFIX_FILENAME.length() + 1 < fileName.indexOf("."))
            {
                storeId = fileName.substring(PREFIX_FILENAME.length() + 1, fileName.indexOf("."));
            }
            int rowLength = result.length;
            for (int i = 0; i < rowLength; i++)
            {
                UserInfo item = new UserInfo();
                item.setGovernmentId(result[i][0]);
                item.setSku(result[i][1]);
                item.setLastName(result[i][2]);
                item.setFirstName(result[i][3]);
                String prefixEmail = Store.getInstance().getStoreMap(storeId).get(Store.PREFIX_EMAIL);
                item.setPrefixEmail(prefixEmail);
                if (result[i].length >= 5 && !"".equals(result[i][4]) && result[i][4] != null && result[i][4].indexOf("OK") < 0)
                {
                    item.setEmailAddress(result[i][4]);
                }
                else
                {
                    item.setEmailAddress(getEmail(i + 1, prefixEmail));
                }
                if (result[i].length >= 6 && !"".equals(result[i][5]) && result[i][5] != null)
                {
                    item.setForwardEmail(result[i][5]);
                }
                item.setPhoneNumber(getPhoneNumber(i + 1));
                item.setStoreNumber(Store.getInstance().getStoreMap(storeId).get(Store.STORE_NUM));
                item.setStoreName(Store.getInstance().getStoreMap(storeId).get(Store.STORE_NAME));
                item.setProductName("iPad");
                item.setQuantity("2");
                LogUtil.getInstance().writeLog(item.toString());
                itemList.add(item);
            }
        }
        catch (FileNotFoundException e)
        {
            e.printStackTrace();
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
    }
    
    private static String getFirstName(int index)
    {
        String name = String.valueOf(index);
        String firstName = "";
        for (int i = 0; i < name.length(); i++)
        {
            int j = Integer.valueOf(name.substring(i, i + 1));
            firstName += names[j];
        }
        return firstName;
    }
    
    private static String getEmail(int i, String prefixEmail)
    {
        String email = String.valueOf(i);
        if (email.length() == 1)
        {
            email = "0" + email;
        }
	    return prefixEmail + email + "@yahoo.cn";
    }
    
    private static String getPhoneNumber(int i)
    {
        String phoneNum = String.valueOf(i);
        if (phoneNum.length() == 1)
        {
            phoneNum = "0" + phoneNum;
        }
        DateFormat dayFormat = new SimpleDateFormat("MMdd");
        return "28" + dayFormat.format(new Date()) + phoneNum;
    }

    /**
     * 
     * 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
     * 
     * @param file
     *            读取数据的源Excel
     * 
     * @param ignoreRows
     *            读取数据忽略的行数，比喻行头不需要读入 忽略的行数为1
     * 
     * @return 读出的Excel中数据的内容
     * 
     * @throws FileNotFoundException
     * 
     * @throws IOException
     */

    public static String[][] getData(File file, int ignoreRows) throws FileNotFoundException, IOException
    {
        List<String[]> result = new ArrayList<String[]>();
        int rowSize = 0;
        BufferedInputStream in = new BufferedInputStream(new FileInputStream(
        file));
        // 打开HSSFWorkbook
        POIFSFileSystem fs = new POIFSFileSystem(in);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFCell cell = null;
        for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++)
        {
            HSSFSheet st = wb.getSheetAt(sheetIndex);
            // 第一行为标题，不取
            for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++)
            {
                HSSFRow row = st.getRow(rowIndex);
                if (row == null)
                {
                    continue;
                }

                int tempRowSize = row.getLastCellNum() + 1;
                if (tempRowSize > rowSize)
                {
                    rowSize = tempRowSize;
                }

                String[] values = new String[rowSize];
                Arrays.fill(values, "");
                boolean hasValue = false;
                for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++)
                {
                    String value = "";
                    cell = row.getCell(columnIndex);
                    if (cell != null)
                    {
                        // 注意：一定要设成这个，否则可能会出现乱码
                        cell.setEncoding(HSSFCell.ENCODING_UTF_16);
                        switch (cell.getCellType())
                        {
                        case HSSFCell.CELL_TYPE_STRING:
                            value = cell.getStringCellValue();
                            break;
                        case HSSFCell.CELL_TYPE_NUMERIC:
                            if (HSSFDateUtil.isCellDateFormatted(cell))
                            {
                                Date date = cell.getDateCellValue();
                                if (date != null)
                                {
                                    value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                                }
                                else
                                {
                                    value = "";
                                }
                            }
                            else
                            {
                                value = new DecimalFormat("0").format(cell.getNumericCellValue());
                            }
                            break;
                        case HSSFCell.CELL_TYPE_FORMULA:
                            // 导入时如果为公式生成的数据则无值
                            if (!cell.getStringCellValue().equals(""))
                            {
                                value = cell.getStringCellValue();
                            }
                            else
                            {
                                value = cell.getNumericCellValue() + "";
                            }
                            break;
                        case HSSFCell.CELL_TYPE_BLANK:
                            break;
                        case HSSFCell.CELL_TYPE_ERROR:
                            value = "";
                            break;
                        case HSSFCell.CELL_TYPE_BOOLEAN:
                            value = (cell.getBooleanCellValue() == true ? "Y" : "N");
                            break;
                        default:
                            value = "";
                        }
                    }

                    if (columnIndex == 0 && value.trim().equals(""))
                    {
                        break;
                    }

                    values[columnIndex] = rightTrim(value);
                    hasValue = true;
                }

                if (hasValue)
                {
                    result.add(values);
                }
            }
        }

        in.close();
        String[][] returnArray = new String[result.size()][rowSize];
        for (int i = 0; i < returnArray.length; i++)
        {
            returnArray[i] = (String[]) result.get(i);
        }
        return returnArray;
    }

    /**
     * 
     * 去掉字符串右边的空格
     * 
     * @param str
     *            要处理的字符串
     * 
     * @return 处理后的字符串
     */

    public static String rightTrim(String str)
    {

        if (str == null)
        {
            return "";
        }

        int length = str.length();
        for (int i = length - 1; i >= 0; i--)
        {
            if (str.charAt(i) != 0x20)
            {
                break;
            }
            length--;
        }
        return str.substring(0, length);
    }
}